package com.authine.cloudpivot.web.api.helper;

import com.alibaba.dubbo.common.utils.CollectionUtils;
import com.alibaba.fastjson.JSONObject;
import com.authine.cloudpivot.engine.api.model.bizmodel.BizPropertyModel;
import com.authine.cloudpivot.engine.api.model.bizquery.BizQueryChildColumnModel;
import com.authine.cloudpivot.engine.api.model.bizquery.BizQueryColumnModel;
import com.authine.cloudpivot.engine.enums.type.BizPropertyType;
import com.authine.cloudpivot.web.api.dubbo.DubboConfigService;
import com.authine.cloudpivot.web.api.util.SpringBeanUtils;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import org.apache.curator.shaded.com.google.common.base.Joiner;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.ResourceUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class FileOperateHelper {
    private static final String FILE_UPLOAD_ATTENTION = "" +
            "1、 单行文本、长文本：直接导入文件内容，若文件内容超出字段设置的长度，会报错\n" +
            "2、 下拉框、单选框、复选框：输入内容，表单子表导入时，需要与选项保持一致，多个选项值之间用“;”隔开\n" +
            "3、 日期：导入输入内容，数值格式支持：2019-05-28或2019-05-28 15:00:00。\n" +
            "4、 数值：导入输入内容，系统根据字段设置格式自动将文件中内容转换格式。如设置格式2.00，文件导入3，自动转换为3.00。\n" +
            "5、 逻辑：填写是或否，“是”是true，“否”是false。\n" +
            "6、 附件、图片、地址、地图定位：不支持导入，下载模板中不此字段。\n" +
            "7、 人员：导入用户的姓名，(防止重复:写入人员加所属部门,例如：姓名.所属部门)，多个用“;”隔开。\n" +
            "8、 部门：部门全路径，如导入云枢2队，写入全部路径：奥哲/研发/云枢/云枢2队，多个部门用“;”隔开。\n" +
            "9、 关联表单：关联的模型的数据标题名称，若名称有相同的多个，选择第一个。\n" +
            "10、主表支持子表导入，子表遵循的规范与主表一致，并且当主表有子表数据项时，导入时主表其他业务数据项至少有一个有值，" +
            "否则这条数据将被过滤。\n"+
            "11、除拥有者外，其他系统字段不允许导入，如果填写了，将在导入时略过处理。\n" +
            "12、数据标题、单据号只作为更新导入时的查询条件，如果填写了，最后导入时不会插入数据处理；" +
                "如果没有设定数据标题/单据号作为查询条件，不允许填写，如果填写了，将报错，导入该条数据失败。\n"+
            "13、关联表单多选：关联的模型的数据标题名称，若名称有相同的多个，选择第一个。\n" +
            "14、时间：导入输入内容，数值格式支持：15:00:00或15:00。\n";

    @Autowired
    private static DubboConfigService dubboConfigService;

    @Value("${cloudpivot.import.data.size}")
    private static Integer importDataSize ;
    /**
     * 上传文件到临时目录
     * @param file
     * @return
     */
    public static String uploadFile(MultipartFile file) {
        String filePath = getFilePath();
        String fileName = System.currentTimeMillis() + file.getOriginalFilename();
        filePath = filePath + "/" + fileName;
        try {
            File fileTemplate = new File(filePath);
            file.transferTo(fileTemplate);
            return fileName;
        } catch (Exception e) {
            log.error("", e);
        }
        return "";
    }

    public static String uploadFile(MultipartFile file,String refId) {
        String filePath = getFilePath();
        String fileName = refId + file.getOriginalFilename();
        filePath = filePath + "/" + fileName;
        try {
            File fileTemplate = new File(filePath);
            file.transferTo(fileTemplate);
            return fileName;
        } catch (Exception e) {
            log.error("", e);
        }
        return "";
    }
    /**
     * 获取需要处理的文件
     * @param fileName
     * @return
     */
    public static File getFile(String fileName) {
        String filePath = getFilePath() + "/" + fileName;
        File fileTemplate = new File(filePath);
        return fileTemplate;
    }

    /**
     * 删除临时文件
     * @param fileName
     * @return
     */
    public static boolean deleteFile(String fileName) {
        boolean flag = false;
        String onlinePath = System.getProperty("user.dir") + File.separator + "upload" + File.separator;
        File upload = new File(onlinePath, "template/");
        if (upload.exists()){
            String path = upload.getAbsolutePath() + "/" + fileName;
            File file = new File(path);
            // 路径为文件且不为空则进行删除
            if (file.isFile()&& file.exists()) {
               flag= file.delete();
            }else {
                flag = true;
            }
        }else {
            flag=true;
        }
        return flag;
    }

    public static Workbook getCommonWb(List<String> headers, List<Map<String, Object>> datas,List<BizQueryColumnModel> headerColumns,String fileName) {
        Workbook wb = null;
        String[] fileNameArray = fileName.split("\\.");
        boolean equals = Objects.equals("xlsx", fileNameArray[1]);
        boolean equals1 = Objects.equals("xls", fileNameArray[1]);
        if (equals){
            wb=new XSSFWorkbook();
        }else {
            wb = new HSSFWorkbook();
        }
        Sheet sheet = wb.createSheet();
        //头标题样式
        CellStyle headStyle = createCellStyle(wb, (short) 13);
        //文本样式
        CellStyle textStyle = createTextCellStyle(wb);
        //设置默认列宽
        sheet.setDefaultColumnWidth(50);
        //填充表头行,生成第一行
        Row row = sheet.createRow(0);
        if (CollectionUtils.isEmpty(headerColumns)){
            for (int i = 0; i < headers.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(headStyle);
                cell.setCellValue(headers.get(i));
            }
        }else {
            List<BizQueryColumnModel> childHeaders = headerColumns.stream().filter(columnModel -> Objects.equals(columnModel.getPropertyType(), BizPropertyType.CHILD_TABLE)).collect(Collectors.toList());
            if (CollectionUtils.isEmpty(childHeaders) || childHeaders.size() < 1) {
                for (int i = 0; i < headerColumns.size() + 1; i++) {
                    Cell cell = row.createCell(i);
                    cell.setCellStyle(headStyle);
                    if (i == headerColumns.size()) {
                        cell.setCellValue("错误信息");
                    } else {
                        cell.setCellValue(headerColumns.get(i).getName());
                    }

                }
            } else {

                int j = 0;
                List<BizQueryColumnModel> childPropertyColumns = new LinkedList<>();
                //子表数据项
                for (BizQueryColumnModel columnModel : headerColumns) {
                    if (columnModel.getPropertyType().equals(BizPropertyType.CHILD_TABLE)) {
                        childPropertyColumns.add(columnModel);
                    } else {
                        j++;
                    }
                }
                List<BizQueryColumnModel> mainHeaders = headerColumns.stream().filter(columnModel -> (!Objects.equals(columnModel.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());
                int mainSize = mainHeaders.size();
                int childSize = 0;
                if (CollectionUtils.isNotEmpty(childHeaders)) {
                    for (BizQueryColumnModel child : childHeaders) {
                        List<BizQueryChildColumnModel> childColumns = child.getChildColumns();
                        if (CollectionUtils.isNotEmpty(childColumns)) {
                            for (BizQueryChildColumnModel childColumnModel : childColumns) {
                                childSize++;
                            }
                        }
                    }
                }
                log.info("主表数量：--{}",mainSize);
                log.info("子表数量：--{}",childSize);

                //填充表头行,生成第一行
//            Row row = sheet.createRow(0);
                //主表标题填充；
                int titleColumn = 0;
                for (int i = 0; i < headerColumns.size(); i++) {
                    if (!headerColumns.get(i).getPropertyType().equals(BizPropertyType.CHILD_TABLE)) {
                        Cell cell = row.createCell(titleColumn);
                        cell.setCellStyle(headStyle);
                        cell.setCellValue(headerColumns.get(i).getName());
                        titleColumn++;
                    }
                }
                int k = 0;
                for (int i = 0; i < childPropertyColumns.size(); i++) {
                    BizQueryColumnModel childTableColumn = childPropertyColumns.get(i);
                    List<BizQueryChildColumnModel> childColumns = childTableColumn.getChildColumns();
                    int size = childColumns.size();
                    for (int z = 0; z < size; z++) {
                        Cell cell = row.createCell(j + k);
                        cell.setCellStyle(headStyle);
                        cell.setCellValue(childTableColumn.getName());
                        k++;
                    }
                }
                //生成第一行错误信息表头行
                Cell cell = row.createCell(mainSize+childSize);
                cell.setCellStyle(headStyle);
                cell.setCellValue("错误信息");

                //填充表头行，生成第二行
                Row childRow = sheet.createRow(1);
                //列表展示非子表数据项创建单元格和入值
                int second = 0;
                for (int i = 0; i < j; i++) {
                    if (headerColumns.get(i).getPropertyType().equals(BizPropertyType.CHILD_TABLE)) {
                        continue;
                    }
                     cell = childRow.createCell(second);
                    cell.setCellStyle(headStyle);
                    cell.setCellValue(headerColumns.get(i).getName());
                    second++;
                }
                //合并主表的表头单元格
                for (int i = 0; i < j; i++) {
                    CellRangeAddress rangeAddress = new CellRangeAddress(0, 1, i, i);
                    sheet.addMergedRegion(rangeAddress);
                }
                //子表数据项创建单元格并入值
                int s = 0;
                for (int i = 0; i < childPropertyColumns.size(); i++) {
                    BizQueryColumnModel childTableColumn = childPropertyColumns.get(i);

                    List<BizQueryChildColumnModel> childColumns = childTableColumn.getChildColumns();
                    int size = childColumns.size();
                    int s1 = s;
                    for (int z = 0; z < size; z++) {
                         cell = childRow.createCell(j + s);
                        cell.setCellStyle(headStyle);
                        BizQueryChildColumnModel bizQueryChildColumnModel = childColumns.get(z);
                        cell.setCellValue(bizQueryChildColumnModel.getName());
                        s++;
                    }
                    if (size > 1) {
                        //主表的子表数据项合并单元格
                        CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, j + s1, j + size + s1 - 1);
                        sheet.addMergedRegion(rangeAddress);
                    }
                }

                //生成第二行并且合并错误信息表头行
                 cell = row.createCell(mainSize+childSize);
                cell.setCellStyle(headStyle);
                cell.setCellValue("错误信息");
                CellRangeAddress rangeAddress = new CellRangeAddress(0, 1, mainSize+childSize,mainSize+ childSize);
                sheet.addMergedRegion(rangeAddress);
            }
        }
        //填充数据
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        // 设置边框颜色
        cellStyle.setLeftBorderColor(IndexedColors.RED.getIndex());
        cellStyle.setRightBorderColor(IndexedColors.RED.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());
        cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
        //设置自动换行
        cellStyle.setWrapText(true);
        for (int i = 0; i < headers.size(); i++) {
            sheet.setDefaultColumnStyle(i, textStyle);
        }
        if (CollectionUtils.isNotEmpty(datas)) {
            createErrorSheetData(datas, sheet, cellStyle, textStyle,headerColumns);
        }
        return wb;
    }

    /**
     * 填充Excel
     * @return
     */
    public static String createFiles(List<String> headers, List<Map<String, Object>> datas, Boolean isChild,List<BizQueryColumnModel> headerColumns) {
        return createFile(headers, datas,isChild,null,headerColumns);
    }
    public static String createFile(List<String> headers, List<Map<String, Object>> datas,Boolean isChild,List<BizPropertyModel> bizPropertyModels,List<BizQueryColumnModel> headerColumns) {
        return createFile(headers, datas,isChild,bizPropertyModels, headerColumns, null);
    }
    /**
     * 在项目同级目录生成excel
     *
     * @param headers
     * @param datas
     * @return
     */
    public static String createFile(List<String> headers, List<Map<String, Object>> datas,Boolean isChild,List<BizPropertyModel> bizPropertyModels,List<BizQueryColumnModel> headerColumns, String fileName) {

        String filePath = getFilePath();
//        String fileName = null;
        if (StringUtils.isEmpty(fileName)) {
            if (isChild) {
                fileName = System.currentTimeMillis() + "临时文件.xlsx";
            } else {
                fileName = System.currentTimeMillis() + "导入错误信息.xlsx";
            }
        }else{
            fileName = fileName+".xlsx";
        }
        filePath = filePath + "/" + fileName;
        try {
            File fileTemplate = new File(filePath);
            OutputStream os = new FileOutputStream(fileTemplate);
            Workbook wb = null;
            if (isChild) {
                wb = getXmlSheetInfo(headers,datas,bizPropertyModels,null);
            } else {
                wb = getCommonWb(headers,datas,headerColumns,fileName);
            }
            wb.write(os);
            os.flush();
            wb.close();
            os.close();
        } catch (FileNotFoundException e) {
            log.error("", e);
        } catch (IOException e) {
            log.error("", e);
        }
        return fileName;
    }

    /**
     * 文件创建扩展
     * @param headers
     * @param datas
     * @return
     */
    public static String createFile(List<String> headers, List<Map<String, Object>> datas) {
        return createFiles(headers,datas,false,null);

    }

    /**
     * 导出服务器文件
     *
     * @param response
     * @param fileName
     */
    public static void downloadErrorInfo(HttpServletResponse response, String fileName) {
        String filePath = getFilePath() + "/" + fileName;
        File fileTemplate = new File(filePath);
        FileInputStream fileInputStream=null;
        byte[] buff = new byte[1024];
        BufferedInputStream bis = null;
        try {
            response.setContentType("application/octet-stream");//设置生成的文件类型
            response.setCharacterEncoding("UTF-8");//设置文件头编码方式和文件名
            response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));
            OutputStream os = response.getOutputStream();
            fileInputStream = new FileInputStream(fileTemplate);
            response.setHeader("Content-Length", String.valueOf(fileInputStream.available()));
            bis = new BufferedInputStream(fileInputStream);
            int i = bis.read(buff);
            while (i != -1) {
                os.write(buff, 0, buff.length);
                i = bis.read(buff);
            }
            os.flush();
            os.close();
        } catch (IOException e) {
            log.error("", e);
        } finally {
            //modified by in 2020-5-19
            if(fileInputStream!=null){
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                    log.error("删除子表临时文件异常",e);
                }
            }
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    log.error("", e);
                }
            }
        }
    }
    public static XSSFWorkbook getXmlSheetInfo( List<String> headers,List<Map<String, Object>> datas) {
        return getXmlSheetInfo(headers,datas);
    }


    //导入sheet模板
    protected static void sheetMainModel(List<BizQueryColumnModel> headerColumns,XSSFWorkbook wb){
        //数据表单
        Sheet sheet = wb.createSheet("数据信息");
        //头标题样式
        CellStyle headStyle = createCellStyle(wb, (short) 13);
        //文本样式
        CellStyle textStyle = createTextCellStyle(wb);
        //设置列宽固定为4个中文字：TODO=>
        sheet.setDefaultColumnWidth(25);
        //填充表头行,生成第一行
        Row row = sheet.createRow(0);
        for (int i = 0; i < headerColumns.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            cell.setCellValue(headerColumns.get(i).getName());
        }
        //填充数据
        CellStyle cellStyle = wb.createCellStyle();
        XSSFDataFormat format = wb.createDataFormat();

        cellStyle.setDataFormat(format.getFormat("@"));

        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setWrapText(true);
        createMainSheetDatas( sheet, cellStyle,  headerColumns);
    }

    //导入sheet模板
    protected static void sheetModel(List<BizQueryColumnModel> headerColumns,XSSFWorkbook wb){

        //数据表单
        Sheet sheet = wb.createSheet("数据信息");
        //头标题样式
        CellStyle headStyle = createCellStyle(wb, (short) 13);
        //文本样式
//        CellStyle textStyle = createTextCellStyle(wb);
        //设置列宽固定为4个中文字：TODO=>
        sheet.setDefaultColumnWidth(25);

        //j ：非子表数据项加上无内容子表数据项的数量
        //childPropertyColumns ：有内容子表数据项内容

        int j = 0;
        List<BizQueryColumnModel> childPropertyColumns = new LinkedList<>();
        //有内容子表数据项
        for (BizQueryColumnModel columnModel:headerColumns) {
            if (columnModel.getPropertyType().equals(BizPropertyType.CHILD_TABLE)&&CollectionUtils.isNotEmpty(columnModel.getChildColumns())&&columnModel.getChildColumns().size()>0){
                childPropertyColumns.add(columnModel);
            }else {
                j++;
            }
        }

        //填充表头行,生成第一行
        Row row = sheet.createRow(0);
        //主表标题填充
        //titleColumn：第一行非子表数据项以及无内容子表数据项的单元格记录值

        int titleColumn =0;
        for (int i = 0; i < headerColumns.size(); i++) {
                if (headerColumns.get(i).getPropertyType().equals(BizPropertyType.CHILD_TABLE)&&CollectionUtils.isNotEmpty(headerColumns.get(i).getChildColumns())&&headerColumns.get(i).getChildColumns().size()>0){
                    continue;
                }
                Cell cell = row.createCell(titleColumn);
                cell.setCellStyle(headStyle);
                cell.setCellValue(headerColumns.get(i).getName());
                titleColumn++;
        }
        //有内容子表数据项标题填充：
        //k：第一行有内容子表数据项 包含的数据项单元格的记录值

        int k = 0;
        for (int i = 0;i<childPropertyColumns.size();i++) {
            BizQueryColumnModel childTableColumn = childPropertyColumns.get(i);
            List<BizQueryChildColumnModel> childColumns = childTableColumn.getChildColumns();
            int size = childColumns.size();
            for (int z=0;z<size;z++){
                Cell cell = row.createCell(j+k);
                cell.setCellStyle(headStyle);
                cell.setCellValue(childTableColumn.getName());
                k++;
            }
        }

        //填充表头行，生成第二行
        Row childRow = sheet.createRow(1);
        //列表展示非子表数据项创建单元格和无内容子表数据项填充标题
        //second：第二行非子表数据项以及无内容子表数据项的单元格记录值
        int second = 0;
        for (int i = 0; i < j; i++) {
            if (headerColumns.get(i).getPropertyType().equals(BizPropertyType.CHILD_TABLE)&&CollectionUtils.isNotEmpty(headerColumns.get(i).getChildColumns())&&headerColumns.get(i).getChildColumns().size()>0){
                continue;
            }
            Cell cell = childRow.createCell(second );
            cell.setCellStyle(headStyle);
            cell.setCellValue(headerColumns.get(i).getName());
            second++;
        }
        //合并主表的表头单元格
        for (int i = 0; i < j; i++) {
            CellRangeAddress rangeAddress = new CellRangeAddress(0,1, i, i);
            sheet.addMergedRegion(rangeAddress);
        }
        //有内容子表数据项创建单元格并填充标题
        //s：第二行有内容子表数据项 包含的数据项单元格的记录值
        //s1：第二行有内容子表数据项 记录单个有内容子表数据项的最大值和最小值，用于合并第一行单个完整子表数据项
        int s = 0;
        for (int i = 0;i<childPropertyColumns.size();i++) {
            BizQueryColumnModel childTableColumn = childPropertyColumns.get(i);

            List<BizQueryChildColumnModel> childColumns = childTableColumn.getChildColumns();
            int size = childColumns.size();
            int s1= s;
            for (int z=0;z<size;z++){
                Cell cell = childRow.createCell(j+s);
                cell.setCellStyle(headStyle);
                BizQueryChildColumnModel bizQueryChildColumnModel = childColumns.get(z);
                cell.setCellValue(bizQueryChildColumnModel.getName());
                s++;
            }
            if (size>1){
                //主表的子表数据项合并单元格
                CellRangeAddress rangeAddress = new CellRangeAddress(0,0, j+s1,j+size+s1-1);
                sheet.addMergedRegion(rangeAddress);
            }
        }
        //格式
        CellStyle cellStyle = wb.createCellStyle();
        XSSFDataFormat format = wb.createDataFormat();

        cellStyle.setDataFormat(format.getFormat("@"));

        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setWrapText(true);
        createSheetDatas( sheet, cellStyle,  headerColumns);
    }

    //导出错误信息
    protected static void sheet(List<Map<String,Object>> datas,List<String> headers,List<BizPropertyModel> bizPropertyModels,XSSFWorkbook wb){
        //数据表单
        Sheet sheet = wb.createSheet("数据信息");
        //头标题样式
        CellStyle headStyle = createCellStyle(wb, (short) 13);
        //文本样式
        CellStyle textStyle = createTextCellStyle(wb);
        //设置列宽固定为4个中文字：TODO=>
        sheet.setDefaultColumnWidth(25);
        //填充表头行,生成第一行
        Row row = sheet.createRow(0);
        if (CollectionUtils.isNotEmpty(datas)){
            headers.add(0,"序号");
        }
        for (int i = 0; i < headers.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            cell.setCellValue(headers.get(i));
        }
        //填充数据
        CellStyle cellStyle = wb.createCellStyle();
        XSSFDataFormat format = wb.createDataFormat();

        cellStyle.setDataFormat(format.getFormat("@"));

        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setWrapText(true);
        if (CollectionUtils.isNotEmpty(datas)){
            createSheetData(datas, sheet, cellStyle, textStyle,bizPropertyModels,wb);
        }

    }
    /**
     * 生成xml信息
     * @param headers
     * @param datas
     * @return
     */
    public static XSSFWorkbook getXmlSheetInfo(List<String> headers, List<Map<String, Object>> datas, List<BizPropertyModel> bizPropertyModels,List<BizQueryColumnModel> headerColumns) {
        XSSFWorkbook wb = new XSSFWorkbook();

        if (CollectionUtils.isNotEmpty(datas)) {
            sheet( datas, headers, bizPropertyModels, wb);
        }else {
            if (CollectionUtils.isEmpty(headerColumns)){
                sheet(datas,headers,bizPropertyModels,wb);
            }else {
                List<BizQueryColumnModel> collect = headerColumns.stream().filter(bizQueryColumnModel -> Objects.equals(bizQueryColumnModel.getPropertyType(), BizPropertyType.CHILD_TABLE)).collect(Collectors.toList());
                if (CollectionUtils.isEmpty(collect)){
                    sheetMainModel(headerColumns,wb);
                }else {
                    sheetModel(headerColumns, wb);
                }
            }
        }
        //模板文件注意事项说明文件
        if (CollectionUtils.isEmpty(datas)) {
            Sheet sheet1 = wb.createSheet("注意事项");
            CellStyle attentionStyle = createCellStyle(wb,HorizontalAlignment.LEFT,VerticalAlignment.TOP);//wb.createCellStyle();
            for (int i = 0; i < 11; i++) { //需要6行表格
                Row attentionRow = sheet1.createRow(i); //创建行
                for (int j = 0; j < 15; j++) {//需要6列
                    attentionRow.createCell(j).setCellStyle(attentionStyle);
                }
            }
            CellRangeAddress callRangeAddress = new CellRangeAddress(1, 16, 0, 18);
            sheet1.addMergedRegion(callRangeAddress);
            CellRangeAddress callRangeAddress1 = new CellRangeAddress(0, 0, 0, 16);
            sheet1.addMergedRegion(callRangeAddress1);
            Row attentionRow = sheet1.getRow(0); //获取第一行
            attentionRow.getCell(0).setCellValue(new XSSFRichTextString("文件导入注意事项"));
            CellStyle attentionFirstRowStyle = createCellStyle(wb,HorizontalAlignment.CENTER,VerticalAlignment.CENTER);
            attentionRow.getCell(0).setCellStyle(attentionFirstRowStyle);
            Row attentionRow1 = sheet1.getRow(1);
            attentionRow1.getCell(0).setCellValue(new XSSFRichTextString(FILE_UPLOAD_ATTENTION));
        }
        return wb;
    }





    private static CellStyle createCellStyle(XSSFWorkbook wb, HorizontalAlignment left, VerticalAlignment top) {
        CellStyle attentionStyle = wb.createCellStyle();
        attentionStyle.setAlignment(left);
        attentionStyle.setVerticalAlignment(top);
        attentionStyle.setWrapText(true);
        return attentionStyle;
    }


    /**
     * 导出数据及文件说明
     *
     * @param response
     * @param headers  导出的头信息
     * @param datas    需要导出的excel数据
     */
    public static void exportData(HttpServletResponse response, List<String> headers, String name, List<Map<String, Object>> datas, List<BizQueryColumnModel> headerColumns,Boolean isChild) {

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String systemTime = sdf.format(new Date());
        String fileName =  name+"导入示例" +systemTime+ ".xlsx";
        log.info("fileName{}"+fileName);
        try {
            response.reset();
            response.setContentType("application/octet-stream");//设置生成的文件类型
            response.setCharacterEncoding("UTF-8");//设置文件头编码方式和文件名
            response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1"));
            response.setHeader("Access-Control-Allow-Origin", "*");
            // 输出流
            OutputStream os = response.getOutputStream();
            // 工作区
            Workbook wb = null;
            if (isChild) {
                wb = getXmlSheetInfo(headers, datas,null,headerColumns);
            } else {
                wb = getCommonWb(headers,datas,headerColumns,fileName);
            }
            wb.write(os);
            wb.close();
            os.flush();
            os.close();
        } catch (Exception e) {
            log.error("", e);
        }
    }
    public static void exportData(HttpServletResponse response, List<String> headers, String name, List<Map<String, Object>> datas, List<BizQueryColumnModel> headerColumns) {
        exportData(response,headers,name,datas,headerColumns,true);

    }

    /**
     * 获取能够导入的数据
     *
     * @param fileName          文件
     * @param headers       文件头信息用于校验
     * @param propertyCodes 用于生成数据的键值
     * @return
     */
    public static List<Map<String, Object>> getImportDatas(String fileName, List<String> headers, List<String> propertyCodes) {
        //一次性只能导入500条数据
        int import_limit = 2001;
        return getImportDatas( fileName, headers, propertyCodes,import_limit);
    }
    public static List<Map<String, Object>> getImportDatas(String fileName, List<String> headers, List<String> propertyCodes, int importLimit) {
        FileInputStream fileInputStream=null;
        try {
            File file = getFile(fileName);
            List<Map<String, Object>> results = new ArrayList<>();
            fileInputStream = new FileInputStream(file);
            Workbook wb = new XSSFWorkbook(fileInputStream);
            Sheet sheet = wb.getSheetAt(0);
            //获取表单头信息
            Row row = sheet.getRow(0);
            List<String> rowHeaders = getRowValue(row);
            int lastRowNum = sheet.getLastRowNum();
            log.debug("导入的有效数据条数：lastRowNum={}", JSONObject.toJSONString(String.valueOf(lastRowNum)));
            //一次性只能导入500条数据
            int import_limit = importLimit;
            String property = SpringBeanUtils.getProperty("cloudpivot.import_limit");
            if(org.apache.commons.lang3.StringUtils.isNotBlank(property)){
                try {
                    import_limit = Integer.parseInt(property);
                } catch (NumberFormatException e) {
                    log.error("property cloudpivot.import_limit is error,import_limit = {}",property);
                }
            }
            if (sheet.getLastRowNum() > import_limit) {
                deleteFile(fileName);
                Map<String, Object> map = new HashMap<>();
                map.put("success", false);
                //文件数目超过限制
                map.put("type", 3);
                results.add(map);
                wb.close();
                return results;
            }
            if (!checkMacheAble(headers, rowHeaders) || CollectionUtils.isEmpty(rowHeaders)) {
                Map<String, Object> map = new HashMap<>();
                //表头不匹配
                map.put("success", false);
                //表头不匹配
                map.put("type", 4);
                results.add(map);
                wb.close();
                return results;
            }
            //获取实际数据项值
            results = getParseData(propertyCodes, sheet);
            wb.close();
            return results;
        } catch (IOException e) {
            log.error("", e);
        }finally {
            //删除临时文件 added in 2020-05-18
            if(fileInputStream!=null){
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            deleteFile(fileName);
        }
        return null;
    }



    /**
     * 获取能够导入的数据
     *
     * @param fileName          文件
     * @param headers       文件头信息用于校验
     * @param propertyCodes 用于生成数据的键值
     * @return
     */
    public static List<Map<String, Object>> getMainImportDatas(String fileName, List<BizQueryColumnModel> headers, List<String> propertyCodes) {
        //一次性只能导入500条数据
        FileInputStream fileInputStream=null;
        try {
            File file = getFile(fileName);
            List<Map<String, Object>> results = new ArrayList<>();
            fileInputStream = new FileInputStream(file);
            Workbook wb = WorkbookFactory.create(fileInputStream);
            Sheet sheet = wb.getSheetAt(0);

            //获取表单头信息,并且区分有子表和（无子表数据表头，有子表无内容）
            Row row = sheet.getRow(0);

            //有内容数据项子表
            List<BizQueryColumnModel> childSize = headers.stream().filter(header -> header != null && (Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))&&(CollectionUtils.isNotEmpty(header.getChildColumns())&&header.getChildColumns().size()>0)).collect(Collectors.toList());
            //无内容数据项子表
            List<BizQueryColumnModel> childEmptySize = headers.stream().filter(header -> header != null && (Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))&&(CollectionUtils.isEmpty(header.getChildColumns())||header.getChildColumns().size()<1)).collect(Collectors.toList());
            //无内容数据项非子表
            List<BizQueryColumnModel> mainSize = headers.stream().filter(header -> header != null && (!Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());
            mainSize.addAll(childEmptySize);
            //主表加无内容子表
            int size = mainSize.size();
            if (CollectionUtils.isEmpty(childSize)) {
                size = row.getPhysicalNumberOfCells();
            }
            List<String> childHeaders = Lists.newArrayList();
            List<String> rowHeaders = getMainRowValue(row,size);
            //子表表头
            if (CollectionUtils.isNotEmpty(childSize)||childSize.size()>0){
                 row = sheet.getRow(1);
                childHeaders = getChildRowValue(row,size);
            }
            rowHeaders.addAll(childHeaders);
            //一次性只能导入5000条数据,指的是500行excel数据，与子表和主表无关
            int lastRowNum = sheet.getLastRowNum();
            log.debug("导入的有效数据条数：lastRowNum={}", JSONObject.toJSONString(String.valueOf(lastRowNum)));
            //一次性只能导入500条数据
            int import_limit = 501;
            String property = SpringBeanUtils.getProperty("cloudpivot.import_limit");
            if(org.apache.commons.lang3.StringUtils.isNotBlank(property)){
                try {
                    import_limit = Integer.parseInt(property);
                } catch (NumberFormatException e) {
                    log.error("property cloudpivot.import_limit is error,import_limit = {}",property);
                }
            }
            if (sheet.getLastRowNum() > import_limit) {
                deleteFile(fileName);
                Map<String, Object> map = new HashMap<>();
                map.put("success", false);
                //文件数目超过限制
                map.put("type", 3);
                map.put("importSize",import_limit);
                results.add(map);
                wb.close();
                return results;
            }
            if (!checkMainMacheAble(headers, rowHeaders) || CollectionUtils.isEmpty(rowHeaders)) {
                Map<String, Object> map = new HashMap<>();
                //表头不匹配
                map.put("success", false);
                //表头不匹配
                map.put("type", 4);
                results.add(map);
                wb.close();
                return results;
            }
            //获取实际数据项值
            results = getMainParseData(propertyCodes, sheet, headers);
            wb.close();
            return results;
        } catch (IOException e) {
            log.error("", e);
        }finally {
            //临时文件删除 added in 2020-05-18
            if(fileInputStream!=null){
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
           deleteFile(fileName);
        }
        return null;
    }

    /**
     * 获取和创建临时文件保存目录,与项目所在目录同级
     */
    public static String getFilePath() {
        try {
            String onlinePath = System.getProperty("user.dir") + File.separator + "upload" + File.separator;
            File upload = new File(onlinePath, "template/");
            if (!upload.exists()) {
	            upload.mkdirs();
            }
            return upload.getAbsolutePath();
        } catch (Exception e) {
	        log.error("", e);
        }
        return "";
    }

    /**
     * 生成json文件保存目录
     * @return
     */
    public static String getJsonFilePath() {
        try {
            String pathPrefix = ResourceUtils.getURL("classpath:").getPath();
            File file = new File(pathPrefix);
            String onlinePath = file.getParentFile().getParentFile().getParent() + File.separator + "webApiLog" + File.separator;
            log.debug("文件位置为" + onlinePath);
            if (!file.exists()) {
	            file = new File("");
            }
            File upload = new File(onlinePath);
            if (!upload.exists()) {
	            upload.mkdirs();
            }
            return upload.getAbsolutePath();
        } catch (FileNotFoundException e) {
            log.error("", e);
        }
        return "";
    }

    /**
     * 通过文件名和json字符按照小时生成json文件
     * @param fileName
     * @param jsonString
     */
    public static void createJsonFile(String fileName,String jsonString) {
        try {
            String filePath = getJsonFilePath() + File.separator + fileName + ".txt";
            log.debug("文件路径为：" + filePath);
            File file = new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            }
            Writer write = new OutputStreamWriter(new FileOutputStream(file,true), "UTF-8");
            write.write(jsonString);
            write.flush();
            write.close();
        } catch (Exception e) {
            log.debug("处理请求数据异常" + e.getMessage());
        }

    }
//    private static void createSheetData(List<String> headers,List<Map<String, Object>> datas, Sheet sheet, CellStyle cellStyle, CellStyle textStyle) {
//        createSheetData(datas,sheet,cellStyle,textStyle,null);
//    }

    /**
     * 创建sheet数据生成表头序号
     *
     * @param datas
     * @param sheet
     * @param cellStyle
     */
    private static void createSheetData(List<Map<String, Object>> datas, Sheet sheet, CellStyle cellStyle,
                                        CellStyle textStyle,List<BizPropertyModel> bizPropertyModels,XSSFWorkbook wb) {

        Boolean isNull = CollectionUtils.isEmpty(bizPropertyModels) ? true : false;
        List<String> numberCodes = null;
        List<String> attachmentCodes = new ArrayList<>();
        if (!isNull) {
            List<BizPropertyModel> collect = bizPropertyModels.get(0).getSubSchema().getProperties().stream().filter(t -> t.getPropertyType().equals(BizPropertyType.NUMERICAL)).collect(Collectors.toList());
            numberCodes = CollectionUtils.isEmpty(collect) ? null: collect.stream().map(BizPropertyModel::getCode).collect(Collectors.toList());

            List<BizPropertyModel> childPropertyList = bizPropertyModels.get(0).getSubSchema().getProperties();
            attachmentCodes = childPropertyList.stream().filter(x->x.getPropertyType()==BizPropertyType.ATTACHMENT)
                    .map(BizPropertyModel::getCode).collect(Collectors.toList());
        }


        for (int j = 0; j < datas.size(); j++) {
            Row Row = sheet.createRow(j + 1);
            Cell cellNumber = Row.createCell(0);
            cellNumber.setCellValue(j + 1);
            cellNumber.setCellStyle(cellStyle);
            int k = 0;
            for (String key : datas.get(j).keySet()) {
                Object value = datas.get(j).get(key);
                Cell cell = Row.createCell(k + 1);
                if (datas.get(j).get(key) != null && datas.get(j).get(key) instanceof Timestamp) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cell.setCellValue(sdf.format(datas.get(j).get(key)));
                } else if (CollectionUtils.isNotEmpty(numberCodes) && numberCodes.contains(key)) {
                    if (checkNumber(datas.get(j).get(key))) {
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue(Double.parseDouble(datas.get(j).get(key).toString()));
                        cell.setCellStyle(cellStyle);
                    } else {
                        cell.setCellValue((datas.get(j).get(key) == null || "null".equals(datas.get(j).get(key))) ? "" : datas.get(j).get(key).toString());
                        cell.setCellStyle(cellStyle);
                    }
                } else if (!Objects.isNull(value) && attachmentCodes.contains(key)){
                    ExportExcelHelper.setAttachmentLink(value,wb,cell);
                } else{
                    cell.setCellValue((datas.get(j).get(key) == null || "null".equals(datas.get(j).get(key))) ? "" : datas.get(j).get(key).toString());
                    cell.setCellStyle(cellStyle);
                }
                k = k + 1;
            }
        }
    }

    private static void createMainSheetDatas(Sheet sheet, CellStyle cellStyle,List<BizQueryColumnModel> headerColumns) {
        for (int j = 0; j < 2; j++) {
            String logical = "是";
            String text = "张三.研发中心;李四.氚云";
            if (j==1){
                logical = "否";
                text="奥哲网络/研发中心;奥哲网络/研发中心/氚云";
            }
            Row Row = sheet.createRow(j + 1);
            int k = 0;
            for (BizQueryColumnModel columnModel : headerColumns) {
                BizPropertyType propertyType = columnModel.getPropertyType();
                Cell cell = Row.createCell(k );
                Boolean defaultProperty = columnModel.getDefaultProperty();
                String name = columnModel.getName();
                if (defaultProperty){
                    switch (name){
                        case "数据标题":
                            cell.setCellValue("数据标题不支持新增导入，可以作为更新导入的查询字段，数据标题存在时做更新导入");
                            cell.setCellStyle(cellStyle);
                            break;
                        case "拥有者":
                            cell.setCellValue("拥有者支持导入，填写拥有者的姓名，例如“张三.所属部门”");
                            cell.setCellStyle(cellStyle);
                            break;
                        case "单据号":
                            cell.setCellValue("单据号不支持新增导入，可以作为更新导入的查询字段，单据号相同时做更新导入");
                            cell.setCellStyle(cellStyle);
                            break;
                        default:
                    }

                }else {
                    template( propertyType,  cell, cellStyle, logical, text);
                }
                k = k + 1;
            }
        }
    }

    private static void createSheetDatas(Sheet sheet, CellStyle cellStyle,List<BizQueryColumnModel> headerColumns) {
        //j : 行
        for (int j = 0; j < 4; j++) {
            String logical = "是";
            String text = "张三.研发中心;李四.氚云";
            if (j==2){
                logical = "否";
                text="奥哲网络/研发中心;奥哲网络/研发中心/氚云";
            }
            List<BizQueryColumnModel> childColumnModels = headerColumns.stream().filter(bizQueryColumnModel -> bizQueryColumnModel != null && Objects.equals(bizQueryColumnModel.getPropertyType(), BizPropertyType.CHILD_TABLE)&&CollectionUtils.isNotEmpty(bizQueryColumnModel.getChildColumns())&&bizQueryColumnModel.getChildColumns().size()>0).collect(Collectors.toList());
            List<BizQueryColumnModel> columns = headerColumns.stream().filter(bizQueryColumnModel -> bizQueryColumnModel != null && (!Objects.equals(bizQueryColumnModel.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());
            int size = columns.size();
            int first = 1;
            if (CollectionUtils.isNotEmpty(childColumnModels)){
                first = 2;
            }
            Row Row = sheet.createRow(j + first);
            int k = 0;
            int childCell = 0;
            for (BizQueryColumnModel columnModel : headerColumns) {
                BizPropertyType propertyType = columnModel.getPropertyType();
                Boolean defaultProperty = columnModel.getDefaultProperty();
                String name = columnModel.getName();
                boolean equals = Objects.equals(propertyType, BizPropertyType.CHILD_TABLE);
                boolean notEmpty = CollectionUtils.isNotEmpty(columnModel.getChildColumns()) && (columnModel.getChildColumns().size() > 0);
                //有内容子表数据项填充内容
                if (equals&&notEmpty){
                    log.info("数据项的名称和是否非空子表数据项：name--{},equals--{},notEmpty--{},childCell--{}",columnModel.getName(),equals,notEmpty,childCell);
                    Integer integer = childTemplate(cellStyle, columnModel, j, logical, text, childCell, Row, size);
                    childCell = integer;
                }else {
                    Cell cell = Row.createCell(k );
                    if (j==1||j==3){
                        cell.setCellValue("");
                        cell.setCellStyle(cellStyle);
                        continue;
                    }
                    if (defaultProperty) {
                        switch (name) {
                            case "数据标题":
                                cell.setCellValue("数据标题不支持新增导入，可以作为更新导入的查询字段，数据标题存在时做更新导入");
                                cell.setCellStyle(cellStyle);
                                break;
                            case "拥有者":
                                cell.setCellValue("拥有者支持导入，填写拥有者的姓名填写，例如“张三.所属部门”");
                                cell.setCellStyle(cellStyle);
                                break;
                            case "单据号":
                                cell.setCellValue("单据号不支持新增导入，可以作为更新导入的查询字段，单据号相同时做更新导入");
                                cell.setCellStyle(cellStyle);
                                break;
                            default:
                        }

                    } else {
                        template( propertyType,  cell, cellStyle, logical, text);
                    }
                    k = k + 1;
                }
            }
        }
    }

    /**
     * 有内容子表数据项示例文件添加
     * @param j 示例文件行号
     * @param columnModel 数据项
     *@param  logical 逻辑型数据项初始值
     *@param text   短文本或者长文本数据项初始值
     * @param childCell 子表单元格
     * @param Row 行号
     * @param size 无内容子表数据项和非子表数据项的数量之和
     */
    private static Integer childTemplate(CellStyle cellStyle,BizQueryColumnModel columnModel,int j,String logical,String text,int childCell ,Row Row,int size){
        List<BizQueryChildColumnModel> childColumns = columnModel.getChildColumns();
        if (j==2||j==0){
            logical = "否";
            text="奥哲网络/研发中心;奥哲网络/研发中心/氚云";
        }
        if (CollectionUtils.isNotEmpty(childColumns)){
            for (BizQueryChildColumnModel childColumnModel: childColumns) {
                int cellColumn = childCell + size;
                Cell cell = Row.createCell(cellColumn);
                BizPropertyType propertyType1 = childColumnModel.getPropertyType();
                childCell = childCell +1;
                switch (propertyType1) {
                    case SHORT_TEXT:
                        cell.setCellValue("这里填写纯文本文字");
                        cell.setCellStyle(cellStyle);
                        break;
                    case LONG_TEXT:
                        cell.setCellValue("这里填写纯长文本文字");
                        cell.setCellStyle(cellStyle);
                        break;
                    case NUMERICAL:
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue(Double.parseDouble("180"));
                        cell.setCellStyle(cellStyle);
                        break;
                    case DATE:
                        cell.setCellValue("2019-10-01 15:23:00");
                        cell.setCellStyle(cellStyle);
                        break;
//                    case TIME:
//                        cell.setCellValue("15:23:00");
//                        cell.setCellStyle(cellStyle);
//                        break;
                    case LOGICAL:
                        cell.setCellValue(logical);
                        cell.setCellStyle(cellStyle);
                        break;
                    case WORK_SHEET:
                        cell.setCellValue("这里填写关联表单数据标题");
                        cell.setCellStyle(cellStyle);
                        break;
                    case SELECTION:
                        cell.setCellValue(text);
                        cell.setCellStyle(cellStyle);
                        break;
                    default:
                        cell.setCellValue("");
                        cell.setCellStyle(cellStyle);
                        break;
                }
            }
        }
        return Integer.valueOf(childCell);
    }

    /**
     * 无内容子表数据项和非子表数据项示例文件添加
     * @param
     * @return
     */
    private static void template(BizPropertyType propertyType, Cell cell,CellStyle cellStyle,String logical,String text){
        switch (propertyType) {
            case SHORT_TEXT:
                cell.setCellValue("这里填写纯文本文字");
                cell.setCellStyle(cellStyle);
                break;
            case LONG_TEXT:
                cell.setCellValue("这里填写纯长文本文字");
                cell.setCellStyle(cellStyle);
                break;
            case NUMERICAL:
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(Double.parseDouble("180"));
                cell.setCellStyle(cellStyle);
                break;
            case DATE:
                cell.setCellValue("2019-10-01 15:23:00");
                cell.setCellStyle(cellStyle);
                break;
//            case TIME:
//                cell.setCellValue("15:23:00");
//                cell.setCellStyle(cellStyle);
//                break;
            case LOGICAL:
                cell.setCellValue(logical);
                cell.setCellStyle(cellStyle);
                break;
            case WORK_SHEET:
                cell.setCellValue("这里填写关联表单数据标题");
                cell.setCellStyle(cellStyle);
                break;
            case MULT_WORK_SHEET:
                cell.setCellValue("这里填写关联表单多选的数据标题名称以;分割");
                cell.setCellStyle(cellStyle);
                break;
            case SELECTION:
                cell.setCellValue(text);
                cell.setCellStyle(cellStyle);
                break;
            default:
                cell.setCellValue("");
                cell.setCellStyle(cellStyle);
                break;
        }
    }


    /**
     * 校验是否数值
     * @param o
     * @return
     */
    private static boolean checkNumber(Object o) {
        if (o == null) {
            return false;
        }
        try {
            Double.parseDouble(o.toString());
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    /**
     * 生成错误信息
     * @param datas
     * @param sheet
     * @param cellStyle
     * @param textStyle
     */
//    private static void createErrorSheetData(List<Map<String, Object>> datas, Sheet sheet, CellStyle cellStyle, CellStyle textStyle) {
//        for (int j = 0; j < datas.size(); j++) {
//            Row Row = sheet.createRow(j + 1);
//            int k = 0;
//            for (String key : datas.get(j).keySet()) {
//                Cell cell = Row.createCell(k);
//                cell.setCellValue((datas.get(j).get(key) == null || "null".equals(datas.get(j).get(key))) ? "" : datas.get(j).get(key).toString());
//                cell.setCellStyle(cellStyle);
//                k = k + 1;
//            }
//            sheet.setDefaultColumnStyle(j, textStyle);
//        }
//    }

    private static void createErrorSheetData(List<Map<String, Object>> datas, Sheet sheet, CellStyle cellStyle, CellStyle textStyle,List<BizQueryColumnModel> headerColumns) {
//      log.info("错误信息集合：--{}",datas);
       if (CollectionUtils.isEmpty(headerColumns)) {
           for (int j = 0; j < datas.size(); j++) {
               Row Row = sheet.createRow(j + 1);
               int k = 0;
               for (String key : datas.get(j).keySet()) {
                   Cell cell = Row.createCell(k);
                   cell.setCellValue((datas.get(j).get(key) == null || "null".equals(datas.get(j).get(key))) ? "" : datas.get(j).get(key).toString());
                   cell.setCellStyle(cellStyle);
                   k = k + 1;
               }
               sheet.setDefaultColumnStyle(j, textStyle);
           }
       }else {
           List<BizQueryColumnModel> collect = headerColumns.stream().filter(bizQueryColumnModel -> Objects.equals(bizQueryColumnModel.getPropertyType(), BizPropertyType.CHILD_TABLE)).collect(Collectors.toList());
           if (CollectionUtils.isEmpty(collect) || collect.size() < 1) {
           for (int j = 0; j < datas.size(); j++) {
               Row Row = sheet.createRow(j + 1);
               int k=0;
               int size = headerColumns.size();
               int errorSize = size + 1;
               for (int i=0;i<errorSize;i++) {
                   BizQueryColumnModel columnModel = null;
                   if (i<size){
                        columnModel = headerColumns.get(i);
                   }
                   Cell cell = Row.createCell(k);
                   cell.setCellStyle(cellStyle);
                   if (i==headerColumns.size()){
                       cell.setCellValue(datas.get(j).get("errInfo" ).toString());
                   }else {
                       cell.setCellValue((datas.get(j).get(columnModel.getPropertyCode()) == null || "null".equals(datas.get(j).get(columnModel.getPropertyCode()))) ? "" : datas.get(j).get(columnModel.getPropertyCode()).toString());
                   }
                   k = k + 1;
               }
           }
           } else {
               //列表非子表数据项的数量
               int childSize = 0;
               List<BizQueryColumnModel> main = headerColumns.stream().filter(bizQueryColumnModel -> !Objects.equals(bizQueryColumnModel.getPropertyType(), BizPropertyType.CHILD_TABLE)).collect(Collectors.toList());
               int mainSize = main.size();
               for (BizQueryColumnModel bizQueryColumnModel:collect) {
                   List<BizQueryChildColumnModel> childColumns = bizQueryColumnModel.getChildColumns();
                   if (CollectionUtils.isNotEmpty(childColumns)){
                       for (BizQueryChildColumnModel bizQueryChildColumnModel:childColumns) {
                           childSize++;
                       }
                   }
               }
               int total = childSize +mainSize;
               int i = 0;
               List<BizQueryColumnModel> childPropertyTypes = new LinkedList<>();
               for (BizQueryColumnModel columnModel : headerColumns) {
                   if (columnModel.getPropertyType().equals(BizPropertyType.CHILD_TABLE)) {
                       childPropertyTypes.add(columnModel);
                   } else {
                       i++;
                   }
               }
               int size = datas.size();
               int row = 2;
               int sNumber = 1;
               List maxChildRow = new LinkedList();
               int childRow = 0;
               for (int j = 0; j < size; j++) {
                   //获取当前行最大行数子表（行数）
                   Integer maxChild = getMaxChildRow(datas, headerColumns, j);
                   childRow = maxChild.intValue();
                   if (childRow < 1) {
                       childRow = 1;
                   }
                   maxChildRow.add(childRow);
                   for (int x = 0; x < childRow; x++) {
                       Row Row = sheet.createRow(row);
                       //填充第一行主表数据
                       if (x==0) {
                           createOwenerSheetData(datas, headerColumns, sNumber, Row, j, cellStyle);
                       }else {
                           createOwenerEmptySheetData(datas, headerColumns, sNumber, Row, j, cellStyle);
                       }
                       //填充第一行子表数据
                       createChildSheetData(datas, childPropertyTypes, i, x, j, Row, cellStyle,total);
                       row++;
                   }
                   sNumber++;
               }
           }
       }
    }

    //第一行主表数据  createOwenerEmptySheetData
    private static void createOwenerSheetData(List<Map<String, Object>> datas, List<BizQueryColumnModel> columns, int sNumber,
                                              Row Row,int j, CellStyle cellStyle) {
        //从map中取值
        log.info("第yi行主表");
        int k =0;
        for(int y = 0;y<columns.size();y++){
            //序号列
//            Cell numberCell = Row.createCell(0);
//            numberCell.setCellValue(sNumber);
//            numberCell.setCellType(CellType.NUMERIC);
            BizQueryColumnModel column = columns.get(y);
            //非子表项
            if (!column.getPropertyType().equals(BizPropertyType.CHILD_TABLE)){
                Map<String, Object> map = datas.get(j);
                String key = column.getPropertyCode();
                Cell cell = Row.createCell(k);
                Object value = map.get(key);
                    cell.setCellStyle(cellStyle);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue((value == null || "null".equals(value)) ? "" : value.toString());
                k++;
            }
        }

    }

    private static void createOwenerEmptySheetData(List<Map<String, Object>> datas, List<BizQueryColumnModel> columns, int sNumber,
                                              Row Row,int j, CellStyle cellStyle) {
        //从map中取值
        int k =0;
        log.info("第二行主表");
        for(int y = 0;y<columns.size();y++){
            BizQueryColumnModel column = columns.get(y);
            //非子表项
            if (!column.getPropertyType().equals(BizPropertyType.CHILD_TABLE)){
                Cell cell = Row.createCell(k);
                cell.setCellStyle(cellStyle);
                cell.setCellType(CellType.STRING);
                cell.setCellValue("");
                k++;
            }
        }

    }
    //一行子表数据
    private static void createChildSheetData(List<Map<String, Object>> datas, List<BizQueryColumnModel> childPropertyTypes,int i,
                                             int x,int j,Row Row,CellStyle cellStyle,int total) {


        int l = i;
        List<Object> list = Lists.newArrayList();
        Map<String, Object> map = datas.get(j);
        //把主表的错误信息存进内存；
        Object errorInfo = map.get("errInfo");
        if (x == 0) {
            if (Objects.nonNull(errorInfo)) {
                List<String> errorInfo1 = (List<String>) errorInfo;
                if (CollectionUtils.isNotEmpty(errorInfo1)&&errorInfo1.size()>0){
                    list.add(errorInfo1);
                }
            }
        }
        log.info("当前行主表的错误信息：--{}",errorInfo);

        for(int y = 0;y<childPropertyTypes.size();y++) {
            BizQueryColumnModel bizQueryColumnModel = childPropertyTypes.get(y);
            String childTableKey = bizQueryColumnModel.getPropertyCode();
            List<Map<String, Object>> childDatas = (List<Map<String, Object>>) map.get(childTableKey);
            if (CollectionUtils.isEmpty(childDatas)) {
                List<BizQueryChildColumnModel> childColumns = bizQueryColumnModel.getChildColumns();
                int size = childColumns.size();

                for (int c = 0; c < size; c++) {
                    Cell cell = Row.createCell(l);
                    cell.setCellStyle(cellStyle);
                    l++;
                }
            } else {
                Map<String, Object> rowChildData = null;
                if (x < childDatas.size()) {
                    rowChildData = childDatas.get(x);
                }
                Object errorInfo2 = null;
                if(rowChildData != null) {
                    errorInfo2 = rowChildData.get("errInfo");
                }
                if (Objects.nonNull(errorInfo2)) {
                    List<String> errorInfo3 = (List<String>) errorInfo2;
                    if (CollectionUtils.isNotEmpty(errorInfo3)&&errorInfo3.size()>0){
                        list.add(errorInfo3);
                    }
                }
                log.info(x+"错误信息列：--{}",errorInfo2);
                List<BizQueryChildColumnModel> childColumns = bizQueryColumnModel.getChildColumns();
                for (BizQueryChildColumnModel childColumn : childColumns) {
                    String childKey = childColumn.getPropertyCode();
                    Cell cell = Row.createCell(l);
                    if (rowChildData == null) {
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue("");
                    } else {
                        Object childValue = rowChildData.get(childKey);
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellStyle(cellStyle);
                        if (childValue != null) {
                            cell.setCellValue(childValue.toString());
                        } else {
                            cell.setCellValue("");
                        }
                    }
                    l++;
                }
            }
        }
        Cell cell = Row.createCell(total);
        cell.setCellStyle(cellStyle);
        String content = Joiner.on("；").join(list);
        log.info("错误信息列：--{}",content);
        cell.setCellValue(content);

    }

    /**
     * 获得当前bizObject的最大子表行数
     *
     * @param
     * @return
     */
    private static Integer getMaxChildRow(List<Map<String, Object>> datas,List<BizQueryColumnModel> columns ,int j){
        List<Integer> childSize = new LinkedList<>();
        Integer max = 0;
        for (BizQueryColumnModel column : columns) {
            if (column.getPropertyType().equals(BizPropertyType.CHILD_TABLE)) {
                Map<String, Object> childMap = datas.get(j);
                String propertyCode = column.getPropertyCode();
                List childRows = (List) childMap.get(propertyCode);
                int size1 = childRows.size();
                Integer integer = Integer.valueOf(size1);
                childSize.add(integer);
            } else {
                continue;
            }
            max = Collections.max(childSize);
        }
        return max;
    }


    /**
     * 表格样式设置
     *
     * @param workbook
     * @param fontsize
     * @return
     */
    private static CellStyle createCellStyle(Workbook workbook, short fontsize) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //创建字体
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }

    /**
     * 创建文本格式
     *
     * @param workbook
     * @return
     */
    private static CellStyle createTextCellStyle(Workbook workbook) {
        //文本模式
        CellStyle textStyle = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        textStyle.setDataFormat(format.getFormat("@"));
        return textStyle;
    }

    /**
     * 获取导入excel文件的值
     *
     * @param propertyCodes
     * @param sheet
     * @return
     */
    private static List<Map<String, Object>> getParseData(List<String> propertyCodes, Sheet sheet) {
        List<Map<String, Object>> maps = new ArrayList<>();
        for (int k = 1; k <= sheet.getLastRowNum(); k++) {
            Map<String, Object> map = new LinkedHashMap<>();
            List<Object> value = Lists.newArrayList();
            Row row = sheet.getRow(k);
            if (row==null){
                continue;
            }
            for (int i = 0; i < propertyCodes.size(); i++) {
                if (sheet.getRow(k).getCell(i) == null) {
                    sheet.getRow(k).createCell(i);
                }
                String stringCellValue = getStringCellValue(sheet.getRow(k).getCell(i));
//                log.info("stringCellValue的数据：--{}",JSONObject.toJSONString(stringCellValue));
                if (!StringUtils.isEmpty(stringCellValue)){
                    value.add(stringCellValue);
                }
                map.put(propertyCodes.get(i), getStringCellValue(sheet.getRow(k).getCell(i)));
            }
            log.debug("value的数据：--{}",value);
            if (CollectionUtils.isEmpty(value)){
                continue;
            }
            maps.add(map);
        }
        return maps;
    }

    /**
     * 获取主表导入excel文件的值
     *
     * @param propertyCodes
     * @param sheet
     * @return
     */
    private static List<Map<String, Object>> getMainParseData(List<String> propertyCodes, Sheet sheet,List<BizQueryColumnModel> headers) {
        List<BizQueryColumnModel> childSize = headers.stream().filter(header -> header != null && (Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());

        int first = 2;
        if (CollectionUtils.isEmpty(childSize)||childSize.size()<1){
            first = 1;
        }
        //确认子表行数,当子表为1时，只考虑主表导入
        if (first == 1){
            List<Map<String, Object>> parseData = getParseData(propertyCodes, sheet);
            return parseData;
        }
        //当有子表数据导入的时候
        List<Map<String, Object>> dataMaps = getMainData(sheet, headers);
        return dataMaps;
    }
    //当有子表的时候
    private static List<Map<String,Object>> getMainData(Sheet sheet,List<BizQueryColumnModel> headers){
        List<BizQueryColumnModel> mainHeaders = headers.stream().filter(header -> header != null && (!Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());
        List<Map<String,Object>> dataMaps = Lists.newArrayList();
        int size = 0;
        if (CollectionUtils.isNotEmpty(mainHeaders)){
            size = mainHeaders.size();
        }
        //记录主表有效对象对应行号
        int lastRowNum = sheet.getLastRowNum();
        log.info("最大行：--{}",lastRowNum);
        //存一个列表的所有子表数据
        List<Map<String,Object>> childDatas = Lists.newArrayList();

        //反向遍历excel数据
        List<Object> empty = Lists.newArrayList();
        for (int k = sheet.getLastRowNum();k>1;k--){
            if (sheet.getRow(k) == null) {
                continue;
            }
            //存每行主表的数据，看key对应的value是否有值
            List<Object> value = Lists.newArrayList();
            //一行excel主表数据
            Map<String, Object> currentMap = getOneExcelMainData(k, sheet, headers, value);
            //一行excel子表数据
            Map<String, Object> oneExcelChildData = getOneExcelChildData(sheet, headers, size, k);
            childDatas.add(oneExcelChildData);
            //当主表单元格有数据时，以此为界限，间隔为一个列表对象
            if (CollectionUtils.isNotEmpty(value)){
                Map<String,List<Map<String,Object>>> childDataMap = Maps.newHashMap();
                List<Map<String,Object>> childList = Lists.newArrayList();
                Collections.reverse(childDatas);
                childList.addAll(childDatas);

                Map<String,Object> mainData = Maps.newHashMap();
                Iterator<Map<String, Object>> iterator = childList.iterator();

                //遍历字表数据组成的list，并根据相同的code为key分组
                while (iterator.hasNext()){
                    Map<String, Object> child = iterator.next();
                    for (String code:child.keySet()) {
                        //map中code已经存在，将该数据存放到同一个key（key存放的是code相同的批次）的map中
                        if(childDataMap.containsKey(code)){
                            Object o1 = child.get(code);
                            Map<String,Object> childProperty =  (Map<String,Object>) o1;
                            childDataMap.get(code).add(childProperty);
                        }else{//map中不存在，新建key，用来存放数据
                            List<Map<String,Object>> list = Lists.newArrayList();
                            Map<String,Object> childProperty = (Map<String, Object>)child.get(code);
                            list.add(childProperty);
                           childDataMap.put(code,list);
                        }
                    }
                }
                for (String code:childDataMap.keySet()){
                    List<Map<String, Object>> list = childDataMap.get(code);
                    log.info("一行主表對應的子表行數：--k={},code={},子表行數={}",k,code,list.size());
                }
                mainData.putAll(childDataMap);
                mainData.putAll(currentMap);
                dataMaps.add(mainData);
                childDatas.clear();
            }
            empty.addAll(value);
        }

        if (CollectionUtils.isEmpty(empty)||empty.size()<1){
            return Lists.newArrayList();
        }
        return dataMaps;
    }

    //一行主表
    private static Map<String,Object> getOneExcelMainData(int k,Sheet sheet,List<BizQueryColumnModel> headers,List<Object> value){
        //只抓取非子表数据
        Map<String,Object> currentMap = Maps.newHashMap();
        List<BizQueryColumnModel> mainHeaders = headers.stream().filter(header -> header != null && (!Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());
        for (int i = 0; i < mainHeaders.size(); i++) {
            if (sheet.getRow(k).getCell(i) == null) {
                sheet.getRow(k).createCell(i);
            }
            String stringCellValue = getStringCellValue(sheet.getRow(k).getCell(i));
//            log.info("stringCellValue的数据：--{}", JSONObject.toJSONString(stringCellValue));
            if (!StringUtils.isEmpty(stringCellValue)) {
                value.add(stringCellValue);
            }
            currentMap.put(mainHeaders.get(i).getPropertyCode(), getStringCellValue(sheet.getRow(k).getCell(i)));
        }
        return currentMap;
    }

    //一个子表数据项的一行
    private static Map<String,Object> getOneChildBizPropertyData(int k,List<BizQueryChildColumnModel> childColumns,int size,int childRow,Sheet sheet ){
        int child = 0;
        Map<String,Object> oneChildMap = Maps.newHashMap();
        for (BizQueryChildColumnModel childColumn:childColumns) {
            int row = child + childRow + size;
            if (sheet.getRow(k).getCell(row) == null) {
                sheet.getRow(k).createCell(row);
            }
            String stringCellValue = getStringCellValue(sheet.getRow(k).getCell(row));
            log.info("stringCellValue的数据：--{}", JSONObject.toJSONString(stringCellValue));
            oneChildMap.put(childColumn.getPropertyCode(), getStringCellValue(sheet.getRow(k).getCell(row)));
            child ++;
        }
        return oneChildMap;
    }

    //子表对象
    private static Map<String,Object> getOneExcelChildData(Sheet sheet,List<BizQueryColumnModel> headers,int size,int k){
        List<BizQueryColumnModel> childHeaders = headers.stream().filter(header -> header != null && (Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());
        //考虑有子表的情况,childRow为主表后的子表的单元格记录的列号初始值
        int childRow = 0;
        Map<String,Object> childDataMap = Maps.newHashMap();
        for (int i = 0; i<  childHeaders.size(); i++) {
            BizPropertyType propertyType = childHeaders.get(i).getPropertyType();
            boolean equals = Objects.equals(propertyType, BizPropertyType.CHILD_TABLE);
            String code = childHeaders.get(i).getPropertyCode();
            List<BizQueryChildColumnModel> childColumns = childHeaders.get(i).getChildColumns();

            if (equals) {
                //一个子表数据
                Map<String, Object> getOneChildBizPropertyData = getOneChildBizPropertyData(k, childColumns, size, childRow, sheet);
                if (CollectionUtils.isNotEmpty(childColumns)&&childColumns.size()>0) {
                    childRow = childRow + childColumns.size();
                }
//                log.info("子表單元格記錄：--{}",childRow);
                childDataMap.put(code,getOneChildBizPropertyData);
            }
        }
        return childDataMap;
    }


    //一行主表数据
//    private static Integer getOneMainData(int firstValidRow,Map<String,Object> currentMap,Map<String,Object> secondMap,Sheet sheet,List<BizQueryColumnModel> headers,int k,Map<String,Object> validRowMap,Map<String,Object> dataMap,List<Object> value){
//       //只抓取非子表数据
//        int lastRow = 0;
//        Object secondValid = validRowMap.get("secondValidRow");
//        int secondValidRow = (int)secondValid;
//        List<BizQueryColumnModel> mainHeaders = headers.stream().filter(header -> header != null && (!Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());
//        if (k>firstValidRow&& MapUtils.isNotEmpty(secondMap)) {
//            currentMap.putAll(secondMap);
//            secondMap.clear();
//        }
//        for (int i = 0; i < mainHeaders.size(); i++) {
//            log.info("当前行的主表数据：--{}",currentMap);
//            log.info("下一个主表对象：--{}",secondMap);
//            if (sheet.getRow(k).getCell(i) == null) {
//                sheet.getRow(k).createCell(i);
//            }
//            String stringCellValue = getStringCellValue(sheet.getRow(k).getCell(i));
//            log.info("stringCellValue的数据：--{}", JSONObject.toJSONString(stringCellValue));
//            if (!StringUtils.isEmpty(stringCellValue)) {
//                value.add(stringCellValue);
//            }
//            if (k==firstValidRow) {
//                currentMap.put(headers.get(i).getPropertyCode(), getStringCellValue(sheet.getRow(k).getCell(i)));
//            }else {
//                secondMap.put(headers.get(i).getPropertyCode(), getStringCellValue(sheet.getRow(k).getCell(i)));
//            }
//
//        }
//        //记录首个主表不为空的值
//        if (CollectionUtils.isNotEmpty(value)) {
//            if (secondValidRow>0) {
//                lastRow = secondValidRow;
//            }
//            validRowMap.clear();
//            validRowMap.put("secondValidRow",k);
//        }
//        dataMap.putAll(currentMap);
//        return Integer.valueOf(lastRow);
//    }

    //子表对象
//    private static Map<String,Object> getOneExcelChildData(Sheet sheet,List<BizQueryColumnModel> headers,int size,int firstValidMain,int secondRow,int k){
//
//        List<BizQueryColumnModel> childHeaders = headers.stream().filter(header -> header != null && (Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());
//
//        log.info("參數：--{},--{},--{},--{},--{}",size,firstValidMain,k,headers,childHeaders);
//        //考虑有子表的情况,childRow为主表后的子表的单元格记录的列号初始值
//        int childRow = 0;
//        Map<String,Object> childDataMap = Maps.newHashMap();
//        for (int i = 0; i<  childHeaders.size(); i++) {
//            BizPropertyType propertyType = childHeaders.get(i).getPropertyType();
//            boolean equals = Objects.equals(propertyType, BizPropertyType.CHILD_TABLE);
//            String code = childHeaders.get(i).getPropertyCode();
//            List<BizQueryChildColumnModel> childColumns = childHeaders.get(i).getChildColumns();
//
//            if (equals) {
//                //firstValidMain 为第一行主表有数据的行号，k：当前行，即下一个有数据的主表行号
//                List<Map<String, Object>> oneChildDataMap = Lists.newArrayList();
//                if (firstValidMain == 0) {
//                    for (int fistChildRow = secondRow; secondRow < k; fistChildRow++) {
//                        Map<String, Object> oneRowExcelChildData = getOneRowExcelChildData(fistChildRow, childColumns, size, childRow, sheet);
//                        log.info("子表一行：--{}", oneRowExcelChildData);
//                        oneChildDataMap.add(oneRowExcelChildData);
//                        log.info("子表對象：--{}", oneRowExcelChildData);
//                    }
//                } else{
//                    for (int fistChildRow = firstValidMain; fistChildRow < k; fistChildRow++) {
//                        //一个子表数据
//                        Map<String, Object> oneRowExcelChildData = getOneRowExcelChildData(fistChildRow, childColumns, size, childRow, sheet);
//                        log.info("子表一行：--{}", oneRowExcelChildData);
//                        oneChildDataMap.add(oneRowExcelChildData);
//                        log.info("子表對象：--{}", oneRowExcelChildData);
//                    }
//            }
//            if (CollectionUtils.isNotEmpty(childColumns)&&childColumns.size()>0) {
//                childRow = childRow + childColumns.size();
//            }
//                log.info("子表單元格記錄：--{}",childRow);
//                childDataMap.put(code,oneChildDataMap);
//            }
//        }
//        return childDataMap;
//    }

    //一个子表对象的一行
//    private static Map<String,Object> getOneRowExcelChildData(int k,List<BizQueryChildColumnModel> childColumns,int size,int childRow,Sheet sheet ){
//        int child = 0;
//        Map<String,Object> oneChildMap = Maps.newHashMap();
//        for (BizQueryChildColumnModel childColumn:childColumns) {
//            int row = child + childRow + size;
//            if (sheet.getRow(k).getCell(row) == null) {
//                sheet.getRow(k).createCell(row);
//            }
//            String stringCellValue = getStringCellValue(sheet.getRow(k).getCell(row));
//            log.info("stringCellValue的数据：--{}", JSONObject.toJSONString(stringCellValue));
//            oneChildMap.put(childColumn.getPropertyCode(), getStringCellValue(sheet.getRow(k).getCell(row)));
//            child ++;
//        }
//        return oneChildMap;
//    }

//    /**
//     * 有效起始行
//     *
//     * @param
//     * @return
//     */
//    private  static  Integer validFirstRow(Sheet sheet,List<BizQueryColumnModel> headers){
//         headers = headers.stream().filter(header -> header != null && (!Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());
//        int firstRow = 0;
//        for (int k = 2; k <= sheet.getLastRowNum(); k++) {
//            List<Object> value = Lists.newArrayList();
//            Row row = sheet.getRow(k);
//            if (row == null) {
//                continue;
//            }
//            for (int i = 0; i < headers.size(); i++) {
//                if (sheet.getRow(k).getCell(i) == null) {
//                    sheet.getRow(k).createCell(i);
//                }
//                String stringCellValue = getStringCellValue(sheet.getRow(k).getCell(i));
//                log.info("stringCellValue的数据：--{}", JSONObject.toJSONString(stringCellValue));
//                if (!StringUtils.isEmpty(stringCellValue)) {
//                    value.add(stringCellValue);
//                }
//            }
//            if (CollectionUtils.isNotEmpty(value)){
//                firstRow = k;
//                Integer first = Integer.valueOf(firstRow);
//                return first;
//            }
//        }
//        return Integer.valueOf(firstRow);
//    }

    /**
     * 将单元格的值转换成字符串
     *
     * @param cell
     * @return
     */
    private static String getStringCellValue(Cell cell) {

        String strCell = null;
        switch (cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    strCell = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
                } else {
                     // DecimalFormat df = new DecimalFormat("0");
                     DecimalFormat df = new DecimalFormat("0.######");
                     boolean isPercent = cell.getCellStyle().getDataFormatString().contains("%");
                    if(isPercent){//假如是百分数
                       if(Objects.nonNull(cell.getNumericCellValue())){
                          BigDecimal decimal = new BigDecimal(cell.getNumericCellValue());
                          decimal = decimal.multiply(new BigDecimal(100));
                          strCell = df.format(decimal);
                       }
                    }else{
                       strCell = String.valueOf(df.format(cell.getNumericCellValue()));
                    }
                    if (StringUtils.isEmpty(strCell) || "null".equals(strCell)) {
                       strCell = null;
                    }
                }
                break;
            case STRING:
                strCell = cell.getStringCellValue();
                break;
            case FORMULA:
                strCell = cell.getCellFormula();
                break;
            case BLANK:
                strCell = null;
                break;
            case BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                strCell = null;
                break;
            default:
                throw new IllegalArgumentException("Invalid cell type " + cell.getCellType());
        }
        if ("".equals(strCell)) {
            strCell = null;
        }
        return strCell;
    }

    /**
     * 校验头信息是否匹配
     *
     * @param headers
     * @param rowHeaders
     * @return
     */
    private static boolean checkMacheAble(List<String> headers, List<String> rowHeaders) {
        if(headers.size()!=rowHeaders.size()){
            return false;
        }
        for (int i = 0; i < headers.size(); i++) {
            String name = headers.get(i);
            boolean equals = Objects.equals(name, rowHeaders.get(i));
            if (!equals) {
                return false;
            }
        }
        return true;
    }

    /**
     * 校验主表头信息是否匹配（1.有子表；2.无子表 )
     * @param headers 表头
     * @param rowHeaders excel 标题
     * @return
     */
    private static boolean checkMainMacheAble(List<BizQueryColumnModel> headers, List<String> rowHeaders) {
        List<BizQueryColumnModel> mainSize = headers.stream().filter(header -> header != null && (!Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))).collect(Collectors.toList());
        List<BizQueryColumnModel> emptyChild = headers.stream().filter(header -> header != null && (Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))&&(CollectionUtils.isEmpty(header.getChildColumns())||header.getChildColumns().size()<1)).collect(Collectors.toList());
        mainSize.addAll(emptyChild);
        List<BizQueryColumnModel> childSize = headers.stream().filter(header -> header != null && (Objects.equals(header.getPropertyType(), BizPropertyType.CHILD_TABLE))&&(CollectionUtils.isNotEmpty(header.getChildColumns())&&header.getChildColumns().size()>0)).collect(Collectors.toList());

        //子表数据项名称
        List<String> names = mainSize.stream().map(BizQueryColumnModel::getName).collect(Collectors.toList());
        if (CollectionUtils.isNotEmpty(childSize)&&childSize.size()>0){
            for (BizQueryColumnModel columnModel:childSize) {
                List<BizQueryChildColumnModel> childColumns = columnModel.getChildColumns();
                    List<String> collect1 = childColumns.stream().map(BizQueryChildColumnModel::getName).collect(Collectors.toList());
                    names.addAll(collect1);
            }
        }
        log.info("names的数据：--{}",names);
        log.info("rowHeaders的数据：--{}",rowHeaders);
        //比较表头名称是否一致
        if (!Objects.equals(names.size(),rowHeaders.size())){
            return false;
        }
        //由于示例文件的标题生成和表头获取的list集合的机制一样，直接逐列标题对比
        for (int i = 0;i<rowHeaders.size(); i++) {
            boolean equals = Objects.equals(rowHeaders.get(i), names.get(i));
            if (!equals){
                return false;
            }
        }
        return true;
    }

    /**
     * 获取每一行的数据
     *
     * @param row
     * @return
     */
    private static List<String> getRowValue(Row row) {
        List<String> rowValues = new ArrayList<>();
        if(row==null){
            return rowValues;
        }
        int number = row.getLastCellNum();
        for (int i = 0; i < number; i++) {
            Cell cell = row.getCell(i);
            String stringCellValue = cell.getStringCellValue();
            log.info("单元格内容：--{}",stringCellValue);
            rowValues.add(stringCellValue);
        }
        return rowValues;
    }

    private static List<String> getMainRowValue(Row row,int size) {
        List<String> rowValues = new ArrayList<>();
        if(row==null){
            return rowValues;
        }
        int number = size ;
        for (int i = 0; i < size; i++) {
            Cell cell = row.getCell(i);
            if (cell==null){
                rowValues.add("");
                continue;
            }
            String stringCellValue = cell.getStringCellValue();
            log.info("单元格内容：--{}",stringCellValue);
            rowValues.add(stringCellValue);
        }
        return rowValues;
    }

    /**
     * 获取每一行的数据
     *
     * @param row
     * @return
     */
    private static List<String> getChildRowValue(Row row,int size) {
        List<String> rowValues = new ArrayList<>();
        if(row==null){
            return rowValues;
        }
        int number = row.getLastCellNum() ;
        for (int i = size; i < number; i++) {
            Cell cell = row.getCell(i);
            if (cell==null){
                rowValues.add("");
                continue;
            }
            String stringCellValue = cell.getStringCellValue();
            log.info("单元格内容：--{}",stringCellValue);
            rowValues.add(stringCellValue);
        }
        return rowValues;
    }

}
